<div class='left'>
    <?php
        $this->widget("AdminThode");
    ?>
</div>
<form action="" method="post" enctype="multipart/form-data">
    <div class="right-main">
        <div class="righ-cont">
            <div class="title_style"> <span><b>Nhập dữ liệu qua file Excel </b></span></div>
            <br>
            <b>Tìm file excel trong máy tính và cập nhật</b>
            <div><input type="file" name="file_excel" size="50"> 
            <br>
            <br>
            <input type="submit" value="Cập nhật"></div>
            <input type="hidden" name="upload" value="yes"/>
            <br />
        </div>

    </div>
</form>

<?php
    set_time_limit(600);
    ini_set('memory_limit', '512M');

    //$upload = getRequest("upload");
    if($upload == "yes"){

        /** Include path **/
        set_include_path(get_include_path() . PATH_SEPARATOR . '../includes/PHPExcel/Classes/');

        /** PHPExcel */
        //require_once 'PHPExcel.php';
        /** PHPExcel_IOFactory */
        //sinclude 'PHPExcel/IOFactory.php';

        $excel_file = $_FILES["file_excel"]["tmp_name"];

        if(strlen($excel_file) < 5){
            echo "<p style=\"font-weight:bold; color:red;\">Chưa có file báo giá</p>";
        }else{

            echo "
            <table cellpadding=3 cellspacing=0 style=\"border-collapse:collapse;\" border=1 bordercolor=#CCC>
            <tr style=\"background-color:#EEE; font-weight:bold;\">
            <td width=30px>STT</td>
            <td width=30px>Mã Sản Phẩm</td>
            <td>Mã Danh Mục</td>
            <td>Tên Sản Phẩm</td>
            <td>Số lượng</td>
            <td>Giá sản phẩm (Đã có VAT)</td>
            <td>Loại tiền tệ</td>
            <td>Giá niêm yết (Đã có VAT)</td>
            <td>Loại tiền tệ</td>
            <td>Bảo hành</td>
            <td>Khuyến mại</td>
            </tr>
            ";

            $pro_count = 0;

            //Upload excel
            $objReader = PHPExcel_IOFactory::createReader('Excel5');
            $objPHPExcel = $objReader->load($excel_file);
            $number_sheet = $objPHPExcel->getSheetCount();
            for($i=0; $i < $number_sheet; $i++){
                $objPHPExcel->setActiveSheetIndex($i);
                $number_row = $objPHPExcel->getActiveSheet()->getHighestRow();

                for($j=6;$j<=$number_row;$j++){
                    $sku = $objPHPExcel->getActiveSheet()->getCell("B".$j)->getValue();
                    $category = $objPHPExcel->getActiveSheet()->getCell("C".$j)->getValue();
                    $name = $objPHPExcel->getActiveSheet()->getCell("D".$j)->getValue();
                    $quantity = $objPHPExcel->getActiveSheet()->getCell("E".$j)->getValue();
                    $price = $objPHPExcel->getActiveSheet()->getCell("F".$j)->getValue();
                    $price = str_replace(".", "", $price);
                    $price = str_replace(",", "", $price);
                    $price = str_replace(" ", "", $price);
                    $currency = $objPHPExcel->getActiveSheet()->getCell("G".$j)->getValue();
                    $price_ny = $objPHPExcel->getActiveSheet()->getCell("H".$j)->getValue();
                    $price_ny = str_replace(".", "", $price_ny);
                    $price_ny = str_replace(",", "", $price_ny);
                    $price_ny = str_replace(" ", "", $price_ny);
                    $currency_ny = $objPHPExcel->getActiveSheet()->getCell("I".$j)->getValue();
                    $warranty = $objPHPExcel->getActiveSheet()->getCell("J".$j)->getValue();
                    $specialOffer = $objPHPExcel->getActiveSheet()->getCell("K".$j)->getValue();

                    if(!empty($sku)){
                        $pro_count ++;

                        //them san pham lan dau
                        if(checkModel($sku)){
                            echo "
                            <tr>
                            <td>".$pro_count."</td>
                            <td>".$sku."</td>
                            <td>".$category."</td>
                            <td>".$name."</td>
                            <td>".$quantity."</td>
                            <td>".format_price($price)."</td>
                            <td>".$currency."</td>
                            <td>".format_price($price_ny)."</td>
                            <td>".$currency_ny."</td>
                            <td>".$warranty."</td>
                            <td>".$specialOffer."</td>
                            </tr>
                            ";
                            //Update
                            //cap nhat thong tin sell product
                            $product_db->query("
                            UPDATE	".TB_PRODUCT." SET                                                

                            name = '".$name."'
                            , nameSearch = '".chuyenKhongDau($name)."'


                            , price_ny = '".$price_ny."'
                            , price_ny_vnd = (SELECT rateToVND*".$price_ny." FROM ".TB_CURRENCY." where currency='".$currency_ny."')
                            , currency_ny = '".$currency_ny."'

                            , specialOffer = '".$specialOffer."'
                            , lastUpdate = now()
                            WHERE sku = '".$sku."'
                            ");

                        }else{
                            echo "
                            <tr>
                            <td>".$pro_count."</td>
                            <td>".$sku."</td>
                            <td>".$category."</td>
                            <td>".$name."</td>
                            <td colspan=7> Đây là sản phẩm tạo mới, cập nhật bằng tay.</td>
                            </tr>
                            ";
                            //Neu chua co model thi tao moi
                            /*$product_db->query("
                            INSERT INTO	".TB_PRODUCT." (
                            sku
                            , category
                            , name
                            , nameSearch
                            , quantity
                            , price
                            , price_vnd
                            , currency
                            , price_ny
                            , price_ny_vnd
                            , currency_ny
                            , warranty
                            , specialOffer
                            , postDate
                            , lastUpdate
                            )VALUES(
                            '".$sku."'
                            , '".$category."'
                            , '".$name."'
                            , '".chuyenKhongdau($name)."'
                            , '".$quantity."'
                            , '".$price."'
                            , (SELECT rateToVND*".$price." FROM ".TB_CURRENCY." where currency='".$currency."')
                            , '".$currency."'
                            , '".$price_ny."'
                            , (SELECT rateToVND*".$price_ny." FROM ".TB_CURRENCY." where currency='".$currency_ny."')
                            , '".$currency_ny."'
                            , '".$warranty."'
                            , '".$specialOffer."'
                            , now()
                            , now()
                            ) ");
                            $productId = mysql_insert_id();
                            $product_db->query("INSERT INTO ".TB_PRODUCT_INFO."(id, spec)VALUES('".$productId."','')");
                            * 
                            */
                        }

                    }
                }
            }
            echo "</table>
            <BR>
            <div style=\"color:#FF0000; font-weight:bold;\">Đã cập nhật xong - Sheet: ".$number_sheet." - Sản phẩm: ".$pro_count." !</div>
            ";
        }
    }


    function checkModel($model){
        $rs = mysql_query("select name from ".TB_PRODUCT." where sku='".$model."' ") or die(mysql_error());
        if ($ll = mysql_fetch_array($rs)){
            return 1;
        }else return 0;
    }
    function checkId($id){
        $rs = mysql_query("select name from ".TB_PRODUCT." where id='".$id."' ") or die(mysql_error());
        if ($ll = mysql_fetch_array($rs)){
            return 1;
        }else return 0;
    }
    function getId($model){
        $rs = mysql_query("select id from ".TB_PRODUCT." where sku='".$model."' ") or die(mysql_error());
        if ($ll = mysql_fetch_array($rs)){
            return $ll[0];
        }else return 0;
    }
?>
